/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package braipetro.data_access;

import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Dictionary;
import java.util.Enumeration;
import java.util.Hashtable;
import java.util.LinkedList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Manasses Lima
 */

enum DataSetState {
    dsInsert, dsEdit, dsBrowse, dsNull
}

public class DataSet {
    private DataConnection connection;
    private Statement stm;
    private ResultSet res = null;
    private ResultSetMetaData rsm;
    private DataSetState state = DataSetState.dsNull;
    
    private String sql = "";
    private String sqlInsert = "";
    private String sqlUpdate = "";
    private String sqlDelete = "";
    private boolean autoApply = true;
    private String table = "";
    private String primaryKey = "";
    
    private int pointer = -1;
    private int savedPointer = -1;
    private boolean first = false;
    private boolean last = false;
    
    private DataSet masterDataSet;
    private String masterKey;
    private String masterField;
    private boolean hasMasterDataSet = false;
    private boolean controlDataSets = false;
    
    //private Dictionary<String, DataSet> dataSets = new Hashtable<String, DataSet>();
    private List<DataSet> dataSets = new LinkedList<>();
    private Dictionary<String, DataField> fields = new Hashtable<String, DataField>();
    private Dictionary<String, DataParameter> params = new Hashtable<String, DataParameter>();
    
    private List<DataRow> rows = new LinkedList();
    private List<DataRow> rowsInserted = new LinkedList<>();
    private List<DataRow> rowsUpdated = new LinkedList<>();
    private List<DataRow> rowsDeleted = new LinkedList<>();
    
//------------------------------------------------------------------------------
// Constructors
//------------------------------------------------------------------------------ 
    public DataSet() {}

    public DataSet(DataConnection cnn) {
        this.connection = cnn;
    }
    
    public DataSet(DataConnection cnn, DataSet pdts) {
        this.connection = cnn;
        setMasterDataSet(pdts);
    }

//------------------------------------------------------------------------------
// GET and SETs
//------------------------------------------------------------------------------    
    
    public DataConnection getConnection() {
        return connection;
    }

    public void setConnection(DataConnection connection) {
        this.connection = connection;
    }

    public String getSql() {
        return sql;
    }

    public void setSql(String sql) {
        this.sql = sql;
    }

    public DataSet getMasterDataSet() {
        return masterDataSet;
    }

    public void setMasterDataSet(DataSet masterDataSet) {
        this.masterDataSet = masterDataSet;
        this.masterDataSet.addDataSet(this);
      
    }

    public String getMasterKey() {
        return masterKey;
    }

    public void setMasterKey(String masterKey) {
        this.masterKey = masterKey;
    }

    public String getMasterField() {
        return masterField;
    }

    public void setMasterField(String masterField) {
        this.masterField = masterField;
    }

    public boolean isAutoApply() {
        return autoApply;
    }

    public void setAutoApply(boolean autoApply) {
        this.autoApply = autoApply;
    }
    

//------------------------------------------------------------------------------
// Fields
//------------------------------------------------------------------------------    
    
    public void addField(String fieldName) {
        DataField field = new DataField();
        field.setField(fieldName);
        fields.put(fieldName, field);
    }
    
    public void addField(String fieldName, String name) {
        DataField field = new DataField();
        field.setField(fieldName);
        field.setName(name);
        
        fields.put(fieldName, field);
    }
    
    public void addField(String fieldName, String name, String type) {
        DataField field = new DataField();
        field.setField(fieldName);
        field.setName(name);
        field.setType(type);
        
        fields.put(name, field);
    }
    
    public void addField(DataField field) {
        fields.put(field.getField(), field);
    }
    
    public DataField getField(String fd) {
        return fields.get(fd);
    }
    
    public void setFieldType(String fd, String type) {
        fields.get(fd).setType(type);
    }
    
//------------------------------------------------------------------------------
// Rows
//------------------------------------------------------------------------------
    
    public DataRow newRow() {
        DataRow row = new DataRow();
        row.setState(DataRowState.dsNEW);
        
        Enumeration enu = fields.keys();
        
        while(enu.hasMoreElements()) {
            row.addColumn(fields.get(enu.nextElement()).getName());
        }
        
        return row;
    }
 
    public void addRow(DataRow row) {
        rows.add(row);
    }
    
    public DataRow getCurrentRow() {
        return rows.get(pointer);
    }
    
    public int numRows() {
        return rows.size();
    }
    
    public boolean removeRow(int idx) {
        boolean resp = false;
        try {
            rows.remove(idx);
            resp = true;
        }
        catch(Exception e) {}
        
        return resp;
    }

    
//------------------------------------------------------------------------------
// Parameters
//------------------------------------------------------------------------------
    
    public void addParameter(DataParameter param) {
        params.put(param.getName(),param);
    }
    
    public void addParameter(String paramName) {
        DataParameter param = new DataParameter(paramName);
        
        params.put(paramName, param);
    }
    
    public void addParameter(String paramName, Object value) {
        DataParameter param = new DataParameter(paramName, value);
        
        params.put(paramName, param);
    }
    
    public void addParameter(String paramName, Object value, DataFieldType type) {
        DataParameter param = new DataParameter();
        param.setName(paramName);
        param.setValue(value);
        param.setType(type);
        
        params.put(paramName, param);
    }
    
    public void addParameter(String paramName, DataFieldType type) {
        DataParameter param = new DataParameter();
        param.setName(paramName);
        param.setType(type);
        
        params.put(paramName, param);
    }
    
    public String getQueryString() {
        String qry = this.sql;
        Enumeration<String> enu = params.keys();
        
        while (enu.hasMoreElements()) {
            String ele = enu.nextElement();
            qry = qry.replace(":" + params.get(ele).getName(), (String)params.get(ele).getString());
        }
        
        return qry;
    }
    
    public void setParamValue(String paramName, Object value) {
        params.get(paramName).setValue(value);
    }
    
    public void setParamType(String paramName, DataFieldType type) {
        params.get(paramName).setType(type);
    }
    
    
    
//------------------------------------------------------------------------------
// Opening and closing
//------------------------------------------------------------------------------
    
    public void open() {
        try {
            
            rowsClear();
            
            stm = connection.getStatement();
            res = stm.executeQuery(getQueryString());
            
            
            rsm = res.getMetaData();
            
            //Verifica se os fields(campos da tabela) estao adicionados e adiciona-os
            if (fields.isEmpty()) {
                
                for (int i = 0; i < rsm.getColumnCount(); i++) {
                    
                    if (table.isEmpty()) {
                        table = rsm.getTableName(i+1);
                        primaryKey = rsm.getColumnName(i+1);
                    }
                    
                    addField(rsm.getColumnName(i+1), rsm.getColumnLabel(i+1), rsm.getColumnTypeName(i+1));
                }    
                
                mountSqlStatements();
                createDetailParameters();
            }
            
            
            
            //Adiciona os registros
            res.beforeFirst();
            
            while (res.next()) {
                DataRow row = newRow();
                
                Enumeration enu = fields.keys();
                while (enu.hasMoreElements()) {
                    String fd = fields.get(enu.nextElement()).getName();
          
                    row.setValue(fd, res.getString(fd));
                }
                
                rows.add(row);
            }
            
            res.close();
            
            state = DataSetState.dsBrowse;
            
            beforeFirst();
            
        } catch (SQLException ex) {
            Logger.getLogger(DataSet.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    
    public void close() {
        rowsClear();
        pointer = -1;
    }
    
    public void reopen() {
        close();
        open();
    }
    
    private void rowsClear() {
        rows.clear();
        rowsDeleted.clear();
        rowsInserted.clear();
        rowsUpdated.clear();
    }
    
//------------------------------------------------------------------------------    
// NAVIGATION
//------------------------------------------------------------------------------
    
    public boolean isFirst() {
        
        return first;
    }
    
    public boolean isLast() {
        return last;
    }
    
    public void beforeFirst() {
        
        if (state ==DataSetState.dsBrowse) {
            pointer = -1;
        }
        
    }
    
    public boolean first() { 
        
        if (state ==DataSetState.dsBrowse) {
            first = true;
            last = false;
            pointer = 0;     

            reloadDataSets();
        }
        else {
            return false;
        } 
           
        return true;
    }
    
    public boolean last() {

        if (state == DataSetState.dsBrowse) {
            first = false;
            last = true;
            pointer = rows.size() - 1;

            reloadDataSets();
        }
        else {
            return false;
        }
        
        return true;
    }
    
    public boolean previous() {
       
        if (state == DataSetState.dsBrowse) {
            if (pointer <= 0) {
                return false;
            }

            pointer -= 1;

            if (last) {
                last = false;
            }

            if (pointer == 0) {
                first = true;
            }

            reloadDataSets();
        }
        else {
            return false;
        }
           
        return true;
    }
    
    public boolean next() {
        if (state == DataSetState.dsBrowse) {
            if (pointer >= rows.size() - 1) {
                return false;
            }

            pointer += 1;

            if (first) {
                first = false;
            }

            if (pointer == rows.size() - 1) {
                last = true;
            }

            reloadDataSets();
        }
        else {
            return false;
        }
        
        return true;
    }
    
    public int getSelectedRow() {
        return pointer;
    }
    
    public int getNumRows() {
        return rows.size();
    }
    
    public void savePos() {
        savedPointer = pointer;
    }
    
    public void gotoSavedPos() {
        pointer = savedPointer;
    }
    
    public void gotoPos(int idx) {
        pointer = idx;
    }
    
//------------------------------------------------------------------------------
// MANIPULATE DATA
//------------------------------------------------------------------------------
    
    public void append() {
        DataRow row = newRow();
        rows.add(row);
        savePos();
        last();
        state = DataSetState.dsInsert;
    }
    
    public void edit() {
        
        rows.get(pointer).saveValues();
        
        state = DataSetState.dsEdit;
        
    }
    
    public void cancel() {
        
        if (state == DataSetState.dsInsert) {
            rows.remove(pointer);
            gotoSavedPos();
        }
        else if (state == DataSetState.dsEdit) {
            rows.get(pointer).restoreValues();
        }
            
        state = DataSetState.dsBrowse;
    }
    
    public void delete() {
        if (autoApply) {
            apply(rows.get(pointer), sqlDelete);
            rows.remove(pointer);
        }
        else {
            rows.get(pointer).delete();
            rowsDeleted.add(rows.get(pointer));
        }
    }
    
    public void post() {
        
        if (state == DataSetState.dsInsert) {
            rows.get(pointer).setState(DataRowState.dsNEW);
            
            if (autoApply) {
                apply(rows.get(pointer), sqlInsert);
            }
            else {
                rowsInserted.add(rows.get(pointer));
            }
        } 
        else if (state == DataSetState.dsEdit) {
            rows.get(pointer).setState(DataRowState.dsEDITED);
            
            if (autoApply) {
                apply(rows.get(pointer), sqlUpdate);
            }
            else {
                rowsUpdated.add(rows.get(pointer));
            }
        }

        state = DataSetState.dsBrowse;
    }
    
    public boolean applyChanges() {
        
        for (DataRow row: rowsInserted) {
            apply(row, sqlInsert);
        }
        
        for (DataRow row: rowsUpdated) {
            apply(row, sqlUpdate);
        }
        
        for (DataRow row: rowsDeleted) {
            apply(row, sqlDelete);
        }
        
        return true;
    }
    
    private boolean apply(DataRow row, String sqlStatement) {
        
        try {
            Enumeration enu = fields.keys();
            
            String str = sqlStatement;
            
            while (enu.hasMoreElements()) {
                DataField field = fields.get(enu.nextElement());
                
                if (field.getType().equals("VARCHAR") || field.getType().equals("DATETIME") || field.getType().equals("TIME") || field.getType().equals("DATE")) {
                    str = str.replace(":" + field.getField(), "'" + row.get(field.getField()) + "'");
                }
                else {
                    str = str.replace(":" + field.getField(), row.get(field.getField()));
                }
                   
            }
            
            stm.execute(str);
            
        } catch (SQLException ex) {
            Logger.getLogger(DataSet.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return true;
    }
    
    public void setValue(String column, String value) {
        rows.get(pointer).setValue(column, value);
    }
    
    public String getValue(String column) {
        return rows.get(pointer).get(column);
    }
    
//------------------------------------------------------------------------------
// Master Detail
//------------------------------------------------------------------------------
    
    public void setMasterDataSet(DataSet dt, String mk) {
        this.masterDataSet = dt;
        this.masterKey = mk;
        this.masterField = mk;  //Caso o masterField seja difernte do masterKey, informar explicitamente
        
        dt.addDataSet(this);   //DataSets filhos
    }
    
    public void addDataSet(DataSet dts) {
        //dataSets.put(dts.get, dts);
        dataSets.add(dts);
        controlDataSets = true;
    }
    
    private void reloadDataSets() {
        if (!dataSets.isEmpty()) {
            //Enumeration<String> enu = dataSets.keys();

            for (DataSet dts: dataSets) {
                dts.setParamValue(primaryKey, this.getValue(primaryKey));
                dts.reopen();
            }
        }
    }
    
    private void createDetailParameters() {
         if (!dataSets.isEmpty()) {
            

            for (DataSet dts: dataSets) {
                dts.addParameter(primaryKey, DataFieldType.dtINTEGER);
            }
        }
    }
    
//------------------------------------------------------------------------------
// Private Methods
//------------------------------------------------------------------------------
    
    private void mountSqlStatements() {
        
        String insertFields = "";
        String insertValues = "";
        String updateSets = "";
        
        Enumeration enu = fields.keys();
        
        boolean firstEle = true;
        
        while (enu.hasMoreElements()) {
            String field = fields.get(enu.nextElement()).getField();
            
            if (firstEle) {
                insertFields += field;
                insertValues += ":" + field;
                updateSets += field + " = :" + field;
                
                firstEle = false;
            }
            else {
                insertFields += ", " + field;
                insertValues += ", :" + field;
                updateSets += ", " + field + " = :" + field;
            }
        }
        
        sqlInsert = "insert into " + table + "(" + insertFields + ") values (" + insertValues + ")";
        sqlUpdate = "update " + table + " set " + updateSets + " where " + primaryKey + " = :" + primaryKey; 
        sqlDelete = "delete from " + table + " where " + primaryKey + " = :" + primaryKey;
        
    }
    
    
    
}